package com.dsz.hse.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.dsz.hse.model.CheckArea;
import com.dsz.hse.model.CleanEnvironment;
import com.dsz.hse.model.Ergonomics;
import com.dsz.hse.model.PersonalProtectiveEquipment;
import com.dsz.hse.model.Procedure;
import com.dsz.hse.model.SecurityCheckRecord;
import com.dsz.hse.model.StaffPosition;
import com.dsz.hse.model.StaffReflect;
import com.dsz.hse.model.ToolsEquipment;

/**
 * @author crjun
 */
@Repository
public class RecordDao extends JdbcDaoSupport implements IRecordDao {
	/*  key=value&key='value'&key like %value%&key like '%value%'*/
    private String getSql(int page, int rows, String filter) {
        String sql = null;
        if (null == filter || filter.trim().equals("")) {
            sql = "select top " + rows + " ID, observateContentTitle,addTime,observateDate,observateTime,observateWorkUnit,observator,staffReflect,personalProtectiveEquipment,proceduree,staffPosition,toolsEquipment,cleanEnvironment,ergonomics,checkArea,rectificationRequest,rectificationPerson,rectificationUnit,completeDate,isStopWork,checkResult from RECORD where ID not in(select top " + (page - 1) * rows + " ID from RECORD  order by ID) order by ID";

        }  else
            sql = "select top " + rows + " ID, observateContentTitle,addTime,observateDate,observateTime,observateWorkUnit,observator,staffReflect,personalProtectiveEquipment,proceduree,staffPosition,toolsEquipment,cleanEnvironment,ergonomics,checkArea,rectificationRequest,rectificationPerson,rectificationUnit,completeDate,isStopWork,checkResult from RECORD where ID not in(select top " + (page - 1) * rows + " ID from RECORD  where " + filter + "  order by ID) and " + filter + " order by ID";
        return sql;
    }
    
	@Override
	public List<SecurityCheckRecord> getPageDutyRecords(String filter, int page, int rows) {
		return (List<SecurityCheckRecord>)getJdbcTemplate().query(getSql(page, rows, filter), new Object[] {}, new DutyRecordRowMapper());
	}

	private static final String ADD_RECORD = "INSERT INTO Record(observateContentTitle,addTime,observateDate,observateTime,observateWorkUnit,observator,staffReflect,personalProtectiveEquipment,proceduree,staffPosition,toolsEquipment,cleanEnvironment,ergonomics,checkArea,rectificationRequest,rectificationPerson,rectificationUnit,completeDate,isStopWork,checkResult) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	@Override
	public void addRecord(SecurityCheckRecord record) {
		getJdbcTemplate().update(
				ADD_RECORD,
				new Object[] { 
						record.getObservateContentTitle(),
						record.getAddTime(),
						record.getObservateDate(),
						record.getObservateTime(),
						record.getObservateWorkUnit(),
						record.getObservator(),
						record.getStaffReflect().toString(),
						record.getPersonalProtectiveEquipment().toString(),
						record.getProcedure().toString(),
						record.getStaffPosition().toString(),
						record.getToolsEquipment().toString(),
						record.getCleanEnvironment().toString(),
						record.getErgonomics().toString(),
						record.getCheckArea().toString(),
						record.getRectificationRequest(),
						record.getRectificationPerson(),
						record.getRectificationUnit(),
						record.getCompleteDate(),
						record.getIsStopWork(),
						record.getCheckResult()
								});
	}

	private static final String REMOVE_RECORD = "DELETE FROM DUTYRECORD WHERE ID = ?";
	@Override
	public void removeRecord(int id) {
		getJdbcTemplate().update(
				REMOVE_RECORD,
				new Object[] { id });
	}
	
	private static final String GET_RECORDS_COUNT = "SELECT COUNT(*) FROM RECORD";

	@Override
	public int getRecordsCount(String filter) {
        if (null == filter || filter.trim().equals("")) {
    		return getJdbcTemplate().queryForInt(GET_RECORDS_COUNT);
        }
		return getJdbcTemplate().queryForInt(GET_RECORDS_COUNT + " WHERE " + filter);
	}

	@Resource(name = "dataSource")
	public void setSuperDataSource(DataSource dataSource) {
		super.setDataSource(dataSource);
	}

	public static class DutyRecordRowMapper implements RowMapper<SecurityCheckRecord> {

		@Override
		public SecurityCheckRecord mapRow(ResultSet rs, int i) throws SQLException {
//			observateContentTitle,addTime,observateDate,observateTime,observateWorkUnit,
//			observator,staffReflect,personalProtectiveEquipment,procedure,staffPosition,
//			toolsEquipment,cleanEnvironment,ergonomics,checkArea,rectificationRequest,
//			rectificationPerson,rectificationUnit
			SecurityCheckRecord record = new SecurityCheckRecord();
			record.setId(rs.getInt("ID"));
			record.setObservateContentTitle(rs.getString("OBSERVATECONTENTTITLE"));
			record.setAddTime(rs.getString("ADDTIME"));
			record.setObservateDate(rs.getString("OBSERVATEDATE"));
			record.setObservateTime(rs.getInt("OBSERVATETIME"));
			record.setObservateWorkUnit(rs.getString("OBSERVATEWORKUNIT"));
			record.setObservator(rs.getString("OBSERVATOR"));
			//////////////////////////////////////////////////////////////
			//////////////////////////////////////////////////////////////
			String[] staffReflect = rs.getString("STAFFREFLECT").split("\\|&&&\\|");
//			return tzgrfhzb 
//					+ "|&&&|" + gbyldwz
//					+ "|&&&|" + cxapgz 
//					+ "|&&&|" + tzgz 
//					+ "|&&&|" + jsdx
//					+ "|&&&|" + ssbq 
//					+ "|&&&|" + qt;
			StaffReflect sf = new StaffReflect();
			sf.setTzgrfhzb(Boolean.parseBoolean(staffReflect[0]));
			sf.setGbyldwz(Boolean.parseBoolean(staffReflect[1]));
			sf.setCxapgz(Boolean.parseBoolean(staffReflect[2]));
			sf.setTzgz(Boolean.parseBoolean(staffReflect[3]));
			sf.setJsdx(Boolean.parseBoolean(staffReflect[4]));
			sf.setSsbq(Boolean.parseBoolean(staffReflect[5]));
			sf.setQt(Boolean.parseBoolean(staffReflect[6]));
			record.setStaffReflect(sf);
			//////////////////////////////////////////////////////////////
			String[] personalProtectiveEquipment = rs.getString("PERSONALPROTECTIVEEQUIPMENT").split("\\|&&&\\|");
//			return yjhyb 
//					+ "|&&&|" + eb
//					+ "|&&&|" + tb 
//					+ "|&&&|" + shsb 
//					+ "|&&&|" + jhtb
//					+ "|&&&|" + hxxt 
//					+ "|&&&|" + qg 
//					+ "|&&&|" + qt;
			PersonalProtectiveEquipment ppe = new PersonalProtectiveEquipment();
			ppe.setYjhyb(Boolean.parseBoolean(personalProtectiveEquipment[0]));
			ppe.setEb(Boolean.parseBoolean(personalProtectiveEquipment[1]));
			ppe.setTb(Boolean.parseBoolean(personalProtectiveEquipment[2]));
			ppe.setShsb(Boolean.parseBoolean(personalProtectiveEquipment[3]));
			ppe.setJhtb(Boolean.parseBoolean(personalProtectiveEquipment[4]));
			ppe.setHxxt(Boolean.parseBoolean(personalProtectiveEquipment[5]));
			ppe.setQg(Boolean.parseBoolean(personalProtectiveEquipment[6]));
			ppe.setQt(Boolean.parseBoolean(personalProtectiveEquipment[7]));
			record.setPersonalProtectiveEquipment(ppe);
			//////////////////////////////////////////////////////////////
			String[] procedure = rs.getString("PROCEDUREE").split("\\|&&&\\|");
//			return myjl 
//					+ "|&&&|" + bxy 
//					+ "|&&&|" + bkhq
//					+ "|&&&|" + ygbzdhblj 
//					+ "|&&&|" + myzzzx 
//					+ "|&&&|" + qt;
			Procedure p = new Procedure();
			p.setMyjl(Boolean.parseBoolean(procedure[0]));
			p.setBxy(Boolean.parseBoolean(procedure[1]));
			p.setBkhq(Boolean.parseBoolean(procedure[2]));
			p.setYgbzdhblj(Boolean.parseBoolean(procedure[3]));
			p.setMyzzzx(Boolean.parseBoolean(procedure[4]));
			p.setQt(Boolean.parseBoolean(procedure[5]));
			record.setProcedure(p);
			//////////////////////////////////////////////////////////////staffPosition
			String[] staffPosition = rs.getString("STAFFPOSITION").split("\\|&&&\\|");
//			return bzj 
//					+ "|&&&|" + bjz 
//					+ "|&&&|" + gczl
//					+ "|&&&|" + bdhhd 
//					+ "|&&&|" + jcjdwddwt 
//					+ "|&&&|" + cd 
//					+ "|&&&|" + jcxrtsyhwz 
//					+ "|&&&|" + bshdzs
//					+ "|&&&|" + jczdsb 
//					+ "|&&&|" + byfhgz 
//					+ "|&&&|" + jczhdsb 
//					+ "|&&&|" + qt;
			StaffPosition sp = new StaffPosition();
			sp.setBzj(Boolean.parseBoolean(staffPosition[0]));
			sp.setBjz(Boolean.parseBoolean(staffPosition[1]));
			sp.setGczl(Boolean.parseBoolean(staffPosition[2]));
			sp.setBdhhd(Boolean.parseBoolean(staffPosition[3]));
			sp.setJcjdwddwt(Boolean.parseBoolean(staffPosition[4]));
			sp.setCd(Boolean.parseBoolean(staffPosition[5]));
			sp.setJcxrtsyhwz(Boolean.parseBoolean(staffPosition[6]));
			sp.setBshdzs(Boolean.parseBoolean(staffPosition[7]));
			sp.setJczdsb(Boolean.parseBoolean(staffPosition[8]));
			sp.setByfhgz(Boolean.parseBoolean(staffPosition[9]));
			sp.setJczhdsb(Boolean.parseBoolean(staffPosition[10]));
			sp.setQt(Boolean.parseBoolean(staffPosition[11]));
			record.setStaffPosition(sp);
			//////////////////////////////////////////////////////////////toolsEquipment
			String[] toolsEquipment = rs.getString("TOOLSEQUIPMENT").split("\\|&&&\\|");
//			return bshgzy 
//					+ "|&&&|" + wzqsygjhsb 
//					+ "|&&&|" + gjhsbbsbaq;
			ToolsEquipment te = new ToolsEquipment();
			te.setBshgzy(Boolean.parseBoolean(toolsEquipment[0]));
			te.setWzqsygjhsb(Boolean.parseBoolean(toolsEquipment[1]));
			te.setGjhsbbsbaq(Boolean.parseBoolean(toolsEquipment[2]));
			record.setToolsEquipment(te);
			//////////////////////////////////////////////////////////////cleanEnvironment
			String[] cleanEnvironment = rs.getString("CLEANENVIRONMENT").split("\\|&&&\\|");
//			return zyqysfzjyx 
//					+ "|&&&|" + gzcssfzjyx 
//					+ "|&&&|" + cljgjdbfsfsd 
//					+ "|&&&|" + qt;
			CleanEnvironment ce = new CleanEnvironment();
			ce.setZyqysfzjyx(Boolean.parseBoolean(cleanEnvironment[0]));
			ce.setGzcssfzjyx(Boolean.parseBoolean(cleanEnvironment[1]));
			ce.setCljgjdbfsfsd(Boolean.parseBoolean(cleanEnvironment[2]));
			ce.setQt(Boolean.parseBoolean(cleanEnvironment[3]));
			record.setCleanEnvironment(ce);
			//////////////////////////////////////////////////////////////ergonomics
			String[] ergonomics = rs.getString("ERGONOMICS").split("\\|&&&\\|");
//			return sffhrtgxxyl 
//					+ "|&&&|" + cfddz
//					+ "|&&&|" + qgwz 
//					+ "|&&&|" + zs 
//					+ "|&&&|" + gzcs
//					+ "|&&&|" + gzqysj 
//					+ "|&&&|" + gjhbs 
//					+ "|&&&|" + zm
//					+ "|&&&|" + zy 
//					+ "|&&&|" + qt;
			Ergonomics e = new Ergonomics();
			e.setSffhrtgxxyl(Boolean.parseBoolean(ergonomics[0]));
			e.setCfddz(Boolean.parseBoolean(ergonomics[1]));
			e.setQgwz(Boolean.parseBoolean(ergonomics[2]));
			e.setZs(Boolean.parseBoolean(ergonomics[3]));
			e.setGzcs(Boolean.parseBoolean(ergonomics[4]));
			e.setGzqysj(Boolean.parseBoolean(ergonomics[5]));
			e.setGjhbs(Boolean.parseBoolean(ergonomics[6]));
			e.setZm(Boolean.parseBoolean(ergonomics[7]));
			e.setZy(Boolean.parseBoolean(ergonomics[8]));
			e.setQt(Boolean.parseBoolean(ergonomics[9]));
			record.setErgonomics(e);
			//////////////////////////////////////////////////////////////checkArea
			String[] checkArea = rs.getString("CHECKAREA").split("\\|&&&\\|");
//			return area 
//					+ "|&&&|" + securityActivity 
//					+ "|&&&|" + notSecurityActivity;
			CheckArea ca = new CheckArea();
			ca.setArea(checkArea[0]);
			ca.setSecurityActivity(checkArea[1]);
			ca.setNotSecurityActivity(checkArea[2]);
			record.setCheckArea(ca);
			record.setRectificationPerson(rs.getString("RECTIFICATIONPERSON"));
			record.setRectificationRequest(rs.getString("rectificationRequest"));
			record.setRectificationUnit(rs.getString("rectificationUnit"));
			record.setCompleteDate(rs.getString("completeDate"));
			record.setIsStopWork(rs.getString("isStopWork"));
			record.setCheckResult(rs.getString("checkResult"));
			return record;
		}

	}
}
